"""
用户相关的数据库操作函数
"""

from datetime import timedelta
from .db import get_db_connection, close_connection


async def get_user_by_username(username):
    """
    根据用户名查询用户信息
    
    Args:
        username: 用户名
    
    Returns:
        dict: 用户信息字典，如果未找到则返回 None
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = "SELECT * FROM users WHERE username = %s"
            await cursor.execute(sql, (username,))
            return await cursor.fetchone()
    except Exception as e:
        print(f"查询用户失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)


async def get_user_by_phone(phone):
    """
    根据手机号获取用户信息
    
    Args:
        phone: 手机号
    
    Returns:
        dict: 用户信息字典，包含ID、用户名和家庭类型
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = "SELECT id, username, family_type FROM users WHERE phone = %s"
            await cursor.execute(sql, [phone])
            user = await cursor.fetchone()
            return user
    except Exception as e:
        print(f"获取用户信息失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)


async def add_user(username, password, phone):
    """
    添加新用户
    
    Args:
        username: 用户名
        password: 密码
        phone: 手机号
    
    Returns:
        bool: 是否添加成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                INSERT INTO users (username, password, phone) 
                VALUES (%s, %s, %s)
            """
            await cursor.execute(sql, (username, password, phone))
        await connection.commit()
        return True
    except Exception as e:
        if connection:
            await connection.rollback()
        print(f"添加用户失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)


async def update_password_by_id(id, new_password):
    """
    通过用户ID修改密码
    
    Args:
        id: 用户ID
        new_password: 新密码
    
    Returns:
        bool: 是否修改成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                UPDATE users 
                SET password = %s
                WHERE id = %s
            """
            await cursor.execute(sql, (new_password, id))
        await connection.commit()
        return True
    except Exception as e:
        if connection:
            await connection.rollback()
        print(f"修改密码失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)


async def update_user_permission(user_id, permission):
    """
    更新用户权限
    
    Args:
        user_id: 用户ID
        permission: 新的权限级别
    
    Returns:
        bool: 是否更新成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                UPDATE users 
                SET family_permissions = %s
                WHERE id = %s
            """
            await cursor.execute(sql, (permission, user_id))
        await connection.commit()
        return True
    except Exception as e:
        if connection:
            await connection.rollback()
        print(f"更新用户权限失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)


async def remove_family_member(user_id):
    """
    移出家庭成员
    
    Args:
        user_id: 用户ID
    
    Returns:
        bool: 是否移出成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                UPDATE users 
                SET family_id = NULL, family_permissions = NULL
                WHERE id = %s
            """
            await cursor.execute(sql, (user_id,))
        await connection.commit()
        return True
    except Exception as e:
        if connection:
            await connection.rollback()
        print(f"移出家庭成员失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)


async def update_user_family(user_id, family_type, permissions):
    """
    更新用户的家庭信息
    
    Args:
        user_id: 用户ID
        family_type: 家庭类型
        permissions: 权限级别
    
    Returns:
        bool: 是否更新成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                UPDATE users 
                SET family_type = %s, family_permissions = %s
                WHERE id = %s
            """
            await cursor.execute(sql, [family_type, permissions, user_id])
            await connection.commit()
            return True
    except Exception as e:
        print(f"更新用户的家庭信息失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

async def verify_code(phone, code, expire_minutes=5):
    """
    创建验证码记录
    
    Args:
        phone: 手机号
        code: 验证码
        expire_minutes: 过期时间（分钟）
    
    Returns:
        bool: 是否创建成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            # 检查是否存在未过期的验证码
            check_sql = """SELECT created_at FROM verification_codes 
                          WHERE phone = %s AND expires_at > NOW()"""
            await cursor.execute(check_sql, (phone,))
            existing_code = await cursor.fetchone()
            
            if existing_code:
                # 检查是否在1分钟冷却期内（使用数据库时间计算）
                cooldown_time = existing_code['created_at'] + timedelta(minutes=1)
                # 从数据库获取当前时间以确保时区一致
                await cursor.execute("SELECT NOW() AS current_db_time")
                db_time = (await cursor.fetchone())['current_db_time']
                if db_time < cooldown_time:
                    return False  # 仍在冷却期不能发送
                
                # 使旧验证码失效
                invalidate_sql = "UPDATE verification_codes SET is_invalid = 1 WHERE phone = %s"
                await cursor.execute(invalidate_sql, (phone,))

            # 插入新验证码
            # 使用数据库时间统一时区
            sql = """INSERT INTO verification_codes 
                    (phone, code, created_at, expires_at, is_invalid)
                    VALUES (%s, %s, NOW(), DATE_ADD(NOW(), INTERVAL %s MINUTE), 0)"""
            await cursor.execute(sql, (phone, code, expire_minutes))
            await connection.commit()
            return True
    except Exception as e:
        print(f"创建验证码失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

async def get_valid_code(phone):
    """
    获取有效验证码
    
    Args:
        phone: 手机号
    
    Returns:
        dict: 验证码信息字典，如果未找到则返回 None
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """SELECT * FROM verification_codes 
                    WHERE phone = %s 
                    AND expires_at > NOW() 
                    AND is_invalid = 0
                    ORDER BY created_at DESC LIMIT 1"""
            await cursor.execute(sql, (phone,))
            return await cursor.fetchone()
    except Exception as e:
        print(f"查询验证码失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)
